Skip Headers

Oracle® HTML DB 2 Day Developer Release 2.0 Part Number B16376-01 |
Home | Book List | Contents | Master Index | Feedback

Previous | Next
| View PDF

# 8 How to Upload and Download Files in an Application

Oracle HTML DB applications may include the ability to upload and download files stored in the database. This tutorial illustrates how to create a form and report with links for file upload and download, how to create and populate a table to store additional attributes about the documents, and finally how to create the mechanism to download the document in your custom table.

This section contains the following topics:

- Creating an Application

- Creating an Upload Form

- Creating a Report with Download Links

- Storing Additional Attributes About the Document

- Store the Document in a Custom Table

- Downloading Documents from the Custom Table

## Creating an Application

First, create a new application using the Create Application Wizard with the assumption you will include an upload form on page 2.

To create an application using the Create Application Wizard:

1. Click the Application Builder icon on the Workspace home page.

2. Click Create.

3. For Method, select Create Application and click Next.

4. For Name:

   1. For Name, enter `Download App`.

   2. For Create Application, select From Scratch.

   3. Click Next.

5. Add a blank page:

   1. Under Select Page Type, select Blank and click Add Page.

      The page appears in the list at the top of the page.

   2. Click Next.

6. For Tabs, select No Tabs and click Next.

7. For Shared Components, accept the defaults and click Next.

8. For Attributes, accept the defaults for Authentication Scheme, Language, and User Language Preferences Derived From and click Next.

9. For User Interface, select Theme 2 and click Next.

10. Click Create.

## Creating an Upload Form

Once you create an application, the next step is to create a form to upload documents. In the following exercise you create a form in an HTML region that contains a file upload item and a button. The button submits the page and returns the user to the same page.

Topics in this section include:

- Create an HTML Region

- Create an Upload Item

- Create a Button

### Create an HTML Region

To create an HTML region:

1. Navigate to the Page Definition for page 1.

2. Under Regions, click the Create icon.

3. For Region, select HTML and click Next.

4. Select a type of HTML region container. Select HTML again and click Next.

5. For Title, enter Submit File and click Next.

6. Accept the remaining defaults and click Create Region.

### Create an Upload Item

To create a file upload item:

1. Navigate to the Page Definition for page 1.

2. Under Items, click the Create icon.

3. For Item Type, select File Browse and click Next.

4. For Display Position and Name:

   1. For Item Name, enter `P1_FILE_NAME`.

   2. For Region, select Submit File.

   3. Click Next.

5. Accept the remaining defaults and click Next.

6. Click Create Item.

### Create a Button

To create a button:

1. Under Buttons, click the Create icon.

2. For Button Region, select Submit File (1) 1 and click Next.

3. For Button Position, select Create a button in a region position and click Next.

4. On Button Attributes:

   1. For Button Name, enter `Submit`.

   2. Accept the remaining defaults.

   3. Click Next.

5. For Button Template, accept the default and click Next.

6. On Display Properties, accept the defaults and click Next.

7. On Branching:

   1. In Branch to Page, select Page 1.

   2. Click Create Button.

8. Run the page by clicking the Run Page icon.

9. When prompted for a user name and password:

   1. For User Name, enter the name of your workspace

   2. For Password, enter the password for your workspace

   3. Click Login.

When you run the page, it should look similar Figure 8-1.

Figure 8-1 Submit File Form

Description of the illustration dn_app_1.gif

## Creating a Report with Download Links

Once you create the form to upload documents, the next step is to create a report on the document table that contains links to download documents. When you use the file upload item type, the files you upload are stored in a table called `wwv_flow_file_objects$`. Every workspace has access to this table through a view called `HTMLDB_APPLICATION_FILES`.

Topics in this section include:

- Create a Report on HTMLDB_APPLICATION_FILES

- Add Link to Download Documents

### Create a Report on HTMLDB_APPLICATION_FILES

To create a report on `HTMLDB_APPLICATION_FILES`:

1. Navigate to the Page Definition for page 1.

2. Under Regions, click the Create icon.

3. For Region, select Report and click Next.

4. For Report Implementation, select SQL Report and click Next.

5. For Title, enter `Uploaded Files` and click Next.

6. On Source, enter the following SQL query:

       SELECT id,name FROM HTMLDB_APPLICATION_FILES

7. Click Create Region.

8. Run the page.

As shown in Figure 8-2, the report you just created shows all documents that have been uploaded. Next, you provide a link to download the document.

Figure 8-2 Uploaded Files Report

Description of the illustration dn_app_1a.gif

### Add Link to Download Documents

To provide a link to download the documents in the report:

1. Navigate to the Page Definition for page 1.

2. Under Regions, click Report adjacent to Uploaded Files.

3. Under Column Attributes, click the Edit icon next to the `ID` column.

4. Scroll down to Column Link. Under Column Link:

   1. In the Link Text field, enter:

          download

   2. From Target, select URL.

   3. In the URL field, enter the following:

          p?n=#ID#

      `#ID#` passes the value contained in the column where `ID` is the column alias.

5. Click Apply Changes.

When you run the page, it should look similar to Figure 8-3.

Figure 8-3 Uploaded Files Report with Download Links

Description of the illustration dn_app_2.gif

## Storing Additional Attributes About the Document

Next, you create another table to store additional information about the documents that are uploaded. In this exercise you:

- Add an item to the upload form to capture the information

- Add a process to insert this information along with the name of the file

- Alter the SQL Report of uploaded files to join to the table containing the additional information

Topics in this section include:

- Create a Table to Store Document Attributes

- Create an Item to Capture the Document Subject

- Create a Process to Insert Information

- Showing Additional Attributes in the Report Region

### Create a Table to Store Document Attributes

To create the table to store additional information about uploaded files:

1. Navigate to the Workspace home page.

2. Click the SQL Workshop icon.

3. Click SQL Commands.

4. In the SQL Command Processor, enter:

       CREATE TABLE file_subjects(name  VARCHAR2(4000), subject VARCHAR2(4000) );

5. Click Run.

### Create an Item to Capture the Document Subject

To create an item to capture the subject of the document:

1. Navigate to the Workspace home page.

2. Click the Application Builder icon.

3. Select Download App.

4. Navigate to the Page Definition for page 1.

5. Under Items, click the Create icon.

6. For Item Type, select Text and click Next.

7. For Text Control Display Type, select Text Field and click Next.

8. For Display Position and Name:

   1. For Item Name, enter `P1_SUBJECT`.

   2. From Region, select Uploaded Files.

   3. Accept the remaining defaults and click Next.

9. For Item Attributes:

   1. In the Label field, enter `Subject`.

   2. Accept the remaining defaults.

   3. Click Next.

10. Click Create Item.

### Create a Process to Insert Information

To create a process to insert the subject information into the new table:

1. Navigate to the Page Definition for page 1.

2. Under Page Processing, Processes, click the Create icon.

3. For Process Type, select PL/SQL and click Next.

4. For Process Attributes:

   1. For Name, enter `Insert`.

   2. From Point, select On Submit - After Computations and Validations.

   3. For Type, select PL/SQL anonymous block.

   4. Click Next.

5. In Enter PL/SQL Page Process, enter the following PL/SQL process:

       INSERT INTO file_subjects(name, subject) VALUES(:P1_FILE_NAME,:P1_SUBJECT);

6. Click Next.

7. For Messages:

   1. In Success Message, enter:

          Subject inserted

   2. In Failure Message enter:

          Error inserting subject

   3. Click Next.

8. From When Button Pressed, select SUBMIT.

9. Click Create Process.

### Showing Additional Attributes in the Report Region

To alter the SQL Report region to join to the additional attributes table:

1. Under Regions, select Uploaded Files.

2. Replace the Region Source with the following:

       SELECT w.id,w.name,s.subject
       FROM HTMLDB_APPLICATION_FILES w,file_subjects s
       WHERE w.name = s.name

3. Click Apply Changes.

   Run the page. As shown in Figure 8-4, the Uploaded Files report now contains a Subject column.

   Figure 8-4 Uploaded Files Report with Subject Column

   Description of the illustration dn_sub_col.gif

## Store the Document in a Custom Table

In certain cases, you may want to store uploaded documents in a table owned by your schema. For example, if you want to create an Oracle Text Index on uploaded documents, you need to store the documents in a custom table.

To store documents in your custom table:

- Add a column of type `BLOB` to hold the document

- Alter the process to insert documents into the custom table

To add a BLOB column to the `file_subjects` table:

1. Navigate to the Workspace home page.

2. Click the SQL Workshop icon.

3. Click SQL Commands.

4. Enter the following SQL statement:

       ALTER TABLE file_subjects ADD(id number,blob_content BLOB,mime_type varchar2(4000) );

5. Click Run.

To alter the process to insert documents into the `file_subjects` table:

1. Navigate to the Workspace home page.

2. Click the Application Builder icon.

3. Select Download App.

4. Navigate to the Page Definition for page 1.

5. Under Processes, select Insert.

6. Under source, replace the process with the following:

       IF ( :P2_FILE_NAME is not null ) THEN
            INSERT INTO file_subjects(id,NAME, SUBJECT, BLOB_CONTENT, MIME_TYPE)
               SELECT ID,:P2_FILE_NAME,:P2_SUBJECT,blob_content,mime_type
                   FROM HTMLDB_APPLICATION_FILES WHERE name = :P2_FILE_NAME;
          DELETE from HTMLDB_APPLICATION_FILES WHERE name = :P2_FILE_NAME;
         END IF;

7. Click Apply Changes.

## Downloading Documents from the Custom Table

Now that documents are being stored in a custom table, you need to provide a way to download them. You do this by creating a procedure and granting execute on that procedure to the pseudo user `HTMLDB_PUBLIC_USER`.

To accomplish this you need to change:

- The SQL report region to no longer join to the `HTMLDB_APPLICATION_FILES` view

- The URL supplied for the ID column in the SQL report to execute the new procedure instead of executing the p procedure

To create a procedure to download documents from the `file_subjects` table and grant execute to public:

1. Navigate to the Workspace home page.

2. Click the SQL Workshop icon.

3. Click SQL Commands.

4. Enter the following SQL statement:

       CREATE OR REPLACE PROCEDURE download_my_file(p_file in number) AS
               v_mime  VARCHAR2(48);
               v_length  NUMBER;
               v_file_name VARCHAR2(2000);
               Lob_loc  BLOB;
       BEGIN
               SELECT MIME_TYPE, BLOB_CONTENT, name,DBMS_LOB.GETLENGTH(blob_content)
                       INTO v_mime,lob_loc,v_file_name,v_length
                       FROM file_subjects
                       WHERE id = p_file;
                     --
                     -- set up HTTP header
                     --
                           -- use an NVL around the mime type and
                           -- if it is a null set it to application/octect
                           -- application/octect may launch a download window from windows
                           owa_util.mime_header( nvl(v_mime,'application/octet'), FALSE );

                       -- set the size so the browser knows how much to download
                       htp.p('Content-length: ' || v_length);
                       -- the filename will be used by the browser if the users does a save as
                       htp.p('Content-Disposition:  attachment; filename="'||substr(v_file_name,instr(v_file_name,'/')+1)|| '"');
                       -- close the headers
                       owa_util.http_header_close;
                       -- download the BLOB
                       wpg_docload.download_file( Lob_loc );
       end download_my_file;
       /

5. Click Run.

6. Enter the following SQL statement:

       GRANT EXECUTE ON download_my_file TO PUBLIC/

7. Click Run.

To change the SQL report region to no longer join with the `HTMLDB_APPLICATION_FILES` view:

1. Navigate to the Page Definition of page 1.

2. Under Regions, select Uploaded Files.

3. Replace the Region Source with the following:

       SELECT s.id,s.name,s.subject FROM file_subjects s

4. Click Apply Changes.

To change the download link to use the new download procedure:

1. Navigate to the Page Definition of page 2.

2. Under Regions, click Report adjacent to Uploaded Files.

3. Next to the ID column, click the Edit icon.

4. Scroll down to the Column Link region.

5. Replace the existing URL with the following:

       #OWNER#.download_my_file?p_file=#ID#

   In this URL:

   - `#OWNER#` is the parsing schema of the current application.

   - `download_my_file` is the new procedure you just created.

   - You are passing in the value of the column ID to the parameter `p_file`.

6. Click Apply Changes.